import pandas as pd
import numpy as np

df = pd.read_excel('任务令/任务令清单0330.xlsx', sheet_name='任务令',
                   usecols=['任务令', 'ICARE发料子库', '待下单量', '维修车间', '需求方*'])
df1 = pd.read_excel('任务令/icare子库基表.xlsx', sheet_name='子库基表',
                    usecols=['SECONDARY_INVENTORY_NAME', '责任库房'])

df = df.merge(df1, left_on='ICARE发料子库', right_on='SECONDARY_INVENTORY_NAME', how='left')
df.loc[df['责任库房'] == '中国区坏件', '任务令'] = 'abcd'
conditions = [
    (df['任务令'] == 'abcd'),
    (df['任务令'].str.startswith('YR', na=False) | df['任务令'].str.startswith('YJ', na=False)),
    (df['任务令'].str.startswith('RC', na=False)),
    (df['ICARE发料子库'].str.startswith('HQ0D2', na=False)),
    (df['ICARE发料子库'].str.startswith('HQ0D1', na=False)),
    (df['需求方*'] == '中国区企业网')
]
values = ['中国区坏件', '中国区原件', '海外原件', '保税坏件', '央库坏件', '企业网坏件']
df['责任库房'] = np.select(conditions, values, default='其他')

df["类型"] = ['无线模块' if '无线模块' in v else '单板与其他' for v in df['维修车间']]
df=df[df['责任库房']!='其他']
dfs = df.pivot_table(index=['责任库房', '类型'], values='待下单量', aggfunc='sum').reset_index()
print(dfs)
